package com.jf.docsify.common.init.db;

import io.undertow.util.FileUtils;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;

import com.jf.docsify.common.exception.DocsifyException;
import com.jf.docsify.common.utils.IOCloseUtil;
import com.jf.docsify.common.utils.JdbcUtil;
import com.jfinal.kit.PropKit;

public abstract class DbInitBaseService {

    private static Connection con = null;

    protected static Map<String, StringBuilder> tablesSqlMap = null;

    public void process() {
        init();
        done();
    }

    protected abstract void init();

    protected abstract String formatUrl(String url);

    protected Connection connection() {
        if (con != null) {
            return con;
        }
        if (tablesSqlMap == null) {
            initTablesSqlMap();
        }
        PropKit.use("config.properties");
        String url = formatUrl(PropKit.get("docsify.db.url"));
        String username = PropKit.get("docsify.db.username");
        String password = PropKit.get("docsify.db.password");
        try {
            Class.forName(driver());
            con = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            DocsifyException.throwE("缺少数据库驱动", e);
        } catch (SQLException e) {
            DocsifyException.throwE("数据库连接失败", e);
        }
        return con;
    }


    protected Map<String, Object> execQuery(String sql, Map<String, Object> params) {
        SqlPara sqlPara = getSqlPara(sql, params);
        return execQuery(sqlPara.getSql(), sqlPara.getParas());
    }

    /**
     * 执行查询语句
     * @param sql 查询语句 eg: select * from tableName where field1=#(field1)
     * @param params
     * @return
     */
    protected Map<String, Object> execQuery(String sql, Object ...params) {
        Connection connection = connection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Map<String, Object> res = new HashMap<>();
        try {
            preparedStatement = connection.prepareStatement(sql);
            log(sql);
            if (params != null && params.length > 0) {
                for (int i = 0; i < params.length; i++) {
                    preparedStatement.setObject(i+1, params[i]);
                }
            }
            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);
                String columnAlias = metaData.getColumnLabel(i);
                Object object;
                try {
                    object = resultSet.getObject(columnName);
                    if (object == null) {
                        object = resultSet.getObject(columnAlias);
                        res.put(lineToHump(columnAlias), object);
                        continue;
                    }
                } catch (Exception e) {
                    object = resultSet.getObject(columnAlias);
                    res.put(lineToHump(columnAlias), object);
                    continue;
                }
                res.put(lineToHump(columnName), object);
            }
        } catch (Exception e) {
            DocsifyException.throwE("执行查询语句异常", e);
        } finally {
            JdbcUtil.close(resultSet);
            JdbcUtil.close(preparedStatement);
        }
        return res;
    }

    /**
     * 执行插入、更新、删除 语句
     * @param sql 待执行语句 eg: update tableName set field1=#(field1) where field2=#(field2)
     * @param params {"field1": "val1", "field2": "val2"}
     * @return
     */
    protected int execUpdate(String sql, Map<String, Object> params) {
        SqlPara sqlPara = getSqlPara(sql, params);
        return execUpdate(sqlPara.getSql(), sqlPara.getParas());
    }

    /**
     * 执行插入、更新、删除 语句
     * @param sql
     * @param params
     * @return
     */
    protected int execUpdate(String sql, Object... params) {
        Connection connection = connection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            log(sql);
            if (params != null && params.length > 0) {
                for (int i = 0; i < params.length; i++) {
                    preparedStatement.setObject(i+1, params[i]);
                }
            }
            return preparedStatement.executeUpdate();
        } catch (Exception e) {
            DocsifyException.throwE("Sql 执行异常", e);
        } finally {
            JdbcUtil.close(preparedStatement);
        }
        return 0;
    }

    protected boolean execSql(String sql) {
        Connection connection = connection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            log(sql);
            return preparedStatement.execute();
        } catch (Exception e) {
            DocsifyException.throwE("Sql 执行异常", e);
        } finally {
            JdbcUtil.close(preparedStatement);
        }
        return false;
    }

    public <T> List<T> execSqlCommand(String sqlCommand) {
        Connection connection = connection();
        Statement preparedStatement = null;
        ResultSet resultSet = null;
        List<T> res = new ArrayList<>();
        try {
            preparedStatement = connection.createStatement();
            resultSet = preparedStatement.executeQuery(sqlCommand);
            log(sqlCommand);
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (resultSet.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnName(i);
                    String columnLabel = metaData.getColumnLabel(i);
                    T t;
                    try {
                        t = (T) resultSet.getObject(columnName);
                        if (t == null) {
                            t = (T) resultSet.getObject(columnLabel);
                        }
                    } catch (Exception e) {
                        t = (T) resultSet.getObject(columnLabel);
                    }
                    res.add(t);
                }
            }
        } catch (Exception e) {
            DocsifyException.throwE("Sql 执行异常", e);
        } finally {
            JdbcUtil.close(resultSet);
            JdbcUtil.close(preparedStatement);
        }
        return res;
    }

    protected void done() {
        JdbcUtil.close(con);
        tablesSqlMap = null;
        con = null;
    }

    protected abstract String sqlScriptName();

    protected void createTable() {
        if (tablesSqlMap == null) {
            initTablesSqlMap();
        }
        Set<String> tableNames = tablesSqlMap.keySet();
        List<String> showTables = showTables();
        if (CollectionUtils.isEmpty(showTables)) {
            showTables = new ArrayList<>();
        }
        List<String> ignoreCreateTables = ignoreCreateTables();
        if (ignoreCreateTables == null) {
            ignoreCreateTables = new ArrayList<>();
        }
        for (String tableName : tableNames) {
            if (ignoreCreateTables.contains(tableName)) {
                continue;
            }
            if (showTables.contains(tableName)) {
                continue;
            }
            StringBuilder tableCreateSql = tablesSqlMap.get(tableName);
            if (tableCreateSql != null) {
                execSql(tableCreateSql.toString());
            }
        }
    }

    protected List<String> ignoreCreateTables(){
        return new ArrayList<>();
    }

    protected abstract List<String> showTables();

    private void initTablesSqlMap() {
        InputStream resourceAsStream = null;
        try {
            resourceAsStream = this.getClassLoader().getResourceAsStream(sqlScriptName());
            String content = FileUtils.readFile(resourceAsStream);
            String[] lines = StringUtils.split(content, "\r");
            boolean ignore = false;
            tablesSqlMap = new HashMap<>();
            StringBuilder sqlBr = null;
            boolean appendSql = false;
            for (int i = 0; i < lines.length; i++) {
                String line = lines[i].replaceAll("\n", "");
                if (line.startsWith("/*")) {
                    ignore = true;
                    continue;
                }
                if (line.endsWith("*/")) {
                    ignore = false;
                    continue;
                }
                if (line.startsWith("--")) {
                    continue;
                }
                if (ignore) {
                    continue;
                }
                if (StringUtils.isBlank(line)) {
                    continue;
                }
                if (line.startsWith("CREATE TABLE")) {
                    appendSql = true;
                    String tableName = line.replaceAll("CREATE TABLE", "").replaceAll("\"", "").replaceAll("\\(", "").replaceAll("`", "").trim();
                    sqlBr = tablesSqlMap.get(tableName);
                    if (sqlBr == null) {
                        sqlBr = new StringBuilder();
                        tablesSqlMap.put(tableName, sqlBr);
                    }
                    sqlBr.append(line);
                    continue;
                }
                if (!appendSql) {
                    continue;
                }
                sqlBr.append(line);
                if (line.endsWith(";")) { // 语句结束
                    sqlBr = null;
                    appendSql = false;
                }
            }
        } catch (Exception e) {
            DocsifyException.throwE("未找到数据库初始化文件", e);
        } finally {
            IOCloseUtil.close(resourceAsStream);
        }
    }

    private ClassLoader getClassLoader() {
        ClassLoader ret = Thread.currentThread().getContextClassLoader();
        return ret != null ? ret : this.getClass().getClassLoader();
    }

    protected abstract String driver();


    protected SqlPara getSqlPara(String sql, Map<String, Object> params) {
        StringBuilder newSqlSb = null;
        List<Object> paras = null;
        if (params != null && params.size() > 0) {
            List<String> keys = new ArrayList<>(params.keySet());
            for (int i = 0; i < keys.size(); i++) {
                String key = keys.get(i);
                sql = sql.replaceAll("\\#\\(" + key + "\\)", "\\#__para__\\#\\__para__\\(" + key + "\\)");
            }
            newSqlSb = new StringBuilder();
            String[] sqlSplit = sql.split("\\#__para__\\#");
            int length = sqlSplit.length;
            paras = new ArrayList<>();
            for (int i = 0; i < length; i++) {
                String sqlPart = sqlSplit[i];
                for (int j = 0; j < keys.size(); j++) {
                    String key = keys.get(j);
                    if (sqlPart.indexOf("__para__(" + key + ")") != -1) {
                        sqlPart = sqlPart.replace("__para__(" + key + ")", "?");
                        paras.add(params.get(key));
                        break;
                    }
                }
                newSqlSb.append(sqlPart);
            }
        }
        return new SqlPara(newSqlSb == null ? sql : newSqlSb.toString(), paras == null ? null : paras.toArray());
    }

    private static final Pattern linePattern = Pattern.compile("_(\\w)");

    /**
     * 下划线转驼峰
     * @param str
     * @return
     */
    public static String lineToHump(String str) {
        str = str.toLowerCase();
        Matcher matcher = linePattern.matcher(str);
        StringBuffer sb = new StringBuffer();
        while (matcher.find()) {
            matcher.appendReplacement(sb, matcher.group(1).toUpperCase());
        }
        matcher.appendTail(sb);
        return sb.toString();
    }

    protected boolean showSql() {
        return PropKit.getBoolean("db.init.showSql", false);
    }
    private void log(String sql) {
        if (showSql()) {
            System.out.println("Sql: " + sql);
        }
    }

    protected class SqlPara {

        public SqlPara(String sql, Object[] paras) {
            this.sql = sql;
            this.paras = paras;
        }

        String sql;

        Object[] paras;

        public String getSql() {
            return sql;
        }

        public Object[] getParas() {
            return paras;
        }
    }
}
